from PyCmpltrtok.common import sep
from typing import List
from typing import Optional
from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user_account"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[Optional[str]]
    addresses: Mapped[List["Address"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )
    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

class Address(Base):
    __tablename__ = "address"
    id: Mapped[int] = mapped_column(primary_key=True)
    email_address: Mapped[str]
    user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
    user: Mapped["User"] = relationship(back_populates="addresses")
    def __repr__(self) -> str:
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"

from sqlalchemy import create_engine
engine = create_engine("sqlite:///demo.tmp.db", echo=True)

sep()
sep()
print(Base.__subclasses__())
sep()
sep()

r = Base.metadata.create_all(engine)
print(r)
"""
2024-04-05 13:04:49,568 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-05 13:04:49,568 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("user_account")
2024-04-05 13:04:49,568 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-05 13:04:49,568 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("user_account")
2024-04-05 13:04:49,568 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-05 13:04:49,568 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("address")
2024-04-05 13:04:49,568 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-05 13:04:49,568 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("address")
2024-04-05 13:04:49,568 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-05 13:04:49,569 INFO sqlalchemy.engine.Engine 
CREATE TABLE user_account (
        id INTEGER NOT NULL, 
        name VARCHAR(30) NOT NULL, 
        fullname VARCHAR, 
        PRIMARY KEY (id)
)


2024-04-05 13:04:49,569 INFO sqlalchemy.engine.Engine [no key 0.00006s] ()
2024-04-05 13:04:49,575 INFO sqlalchemy.engine.Engine 
CREATE TABLE address (
        id INTEGER NOT NULL, 
        email_address VARCHAR NOT NULL, 
        user_id INTEGER NOT NULL, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user_account (id)
)


2024-04-05 13:04:49,575 INFO sqlalchemy.engine.Engine [no key 0.00007s] ()
2024-04-05 13:04:49,580 INFO sqlalchemy.engine.Engine COMMIT
None
"""

"""
When running with tables existed:

2024-04-05 13:06:55,055 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-05 13:06:55,055 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("user_account")
2024-04-05 13:06:55,055 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-05 13:06:55,056 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("address")
2024-04-05 13:06:55,056 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-05 13:06:55,056 INFO sqlalchemy.engine.Engine COMMIT
None
"""

from sqlalchemy.orm import Session

with Session(engine) as session:
    spongebob = User(
        name="spongebob",
        fullname="Spongebob Squarepants",
        addresses=[Address(email_address="spongebob@sqlalchemy.org")],
    )
    sandy = User(
        name="sandy",
        fullname="Sandy Cheeks",
        addresses=[
            Address(email_address="sandy@sqlalchemy.org"),
            Address(email_address="sandy@squirrelpower.org"),
        ],
    )
    patrick = User(name="patrick", fullname="Patrick Star")
    session.add_all([spongebob, sandy, patrick])
    session.commit()

"""
2024-04-05 13:04:49,584 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-05 13:04:49,585 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
2024-04-05 13:04:49,585 INFO sqlalchemy.engine.Engine [generated in 0.00008s (insertmanyvalues) 1/3 (ordered; batch not supported)] ('spongebob', 'Spongebob Squarepants')
2024-04-05 13:04:49,585 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
2024-04-05 13:04:49,585 INFO sqlalchemy.engine.Engine [insertmanyvalues 2/3 (ordered; batch not supported)] ('sandy', 'Sandy Cheeks')
2024-04-05 13:04:49,585 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
2024-04-05 13:04:49,586 INFO sqlalchemy.engine.Engine [insertmanyvalues 3/3 (ordered; batch not supported)] ('patrick', 'Patrick Star')
2024-04-05 13:04:49,586 INFO sqlalchemy.engine.Engine INSERT INTO address (email_address, user_id) VALUES (?, ?) RETURNING id
2024-04-05 13:04:49,586 INFO sqlalchemy.engine.Engine [generated in 0.00006s (insertmanyvalues) 1/3 (ordered; batch not supported)] ('spongebob@sqlalchemy.org', 1)
2024-04-05 13:04:49,586 INFO sqlalchemy.engine.Engine INSERT INTO address (email_address, user_id) VALUES (?, ?) RETURNING id
2024-04-05 13:04:49,586 INFO sqlalchemy.engine.Engine [insertmanyvalues 2/3 (ordered; batch not supported)] ('sandy@sqlalchemy.org', 2)
2024-04-05 13:04:49,587 INFO sqlalchemy.engine.Engine INSERT INTO address (email_address, user_id) VALUES (?, ?) RETURNING id
2024-04-05 13:04:49,587 INFO sqlalchemy.engine.Engine [insertmanyvalues 3/3 (ordered; batch not supported)] ('sandy@squirrelpower.org', 2)
2024-04-05 13:04:49,587 INFO sqlalchemy.engine.Engine COMMIT
"""